﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using MandalaySoft.Common;
using System.Data.SqlClient;
using System.Data;

namespace SIMS.BL
{
    public class GradeLevelDB
    {
        MyDatabase _db = new MyDatabase();

        public DataTable GetGradeLevelList(string filter, int SchoolID, bool IsFullName, out MyError error)
        {
            DataTable _dt = new DataTable("tblSchoolName");
            string strSQLStatement = "SELECT GradeLevelID";
            if (IsFullName)
            {
                strSQLStatement += ", Title As GradeLevelName";
            }
            else
            {
                strSQLStatement += ", ShortName As GradeLevelName ";
            }
            strSQLStatement += " FROM tblGradeLevel ";

            if (SchoolID != 0)
            {
                strSQLStatement += " WHERE SchoolID = " + SchoolID.ToString();
            }
            
            if (filter != string.Empty)
            {
                if (SchoolID == 0)
                {
                    strSQLStatement += " WHERE " + filter;
                }
                else
                {
                    strSQLStatement += " AND " + filter;
                }
            }
            else
            {
                if (SchoolID == 0)
                {
                    strSQLStatement += " WHERE IsActive = 1 ";
                }
                else
                {
                    strSQLStatement += " AND IsActive = 1 ";
                }
            }

            _dt = _db.GetDataTable(strSQLStatement, CommandType.Text, new SortedList(), out error);
            return _dt;
        }

        public DataTable GetGradeLevelList(int SchoolID, out MyError error)
        {
            DataTable _dt = new DataTable("tblGradeLevel");
            SortedList _paramList = new SortedList();
            _paramList.Add("@SchoolID", SchoolID);

            _dt = _db.GetDataTable("sprocGradeLevelSelectList", CommandType.StoredProcedure, _paramList, out error);
            return _dt;
        }

        public int Delete(int GradeLevelID, out MyError error)
        {
            SortedList _paramList = new SortedList();
            _paramList.Add("@GradeLevelID", GradeLevelID);

            return _db.ExecuteNonSql("sprocGradeLevelDeleteSingleItem", CommandType.StoredProcedure, _paramList, out error);
        }

        public GradeLevel GetItem(int GradeLevelID)
        {
            GradeLevel item = new GradeLevel();

            SortedList _paraList = new SortedList();
            _paraList.Add("@GradeLevelID", GradeLevelID);

            MyError _error = new MyError();
            SqlDataReader _reader = _db.GetSqlDataReader("sprocGradeLevelSelectSingleItem", System.Data.CommandType.StoredProcedure, _paraList, out _error);
            item.Error = _error;

            if (_error.ErrorNo == 0 && _reader.Read())
            {
                item = FillData(_reader, item);
            }

            return item;
        }

        public GradeLevel Save(GradeLevel item)
        {
            try
            {
                SortedList _paraList = new SortedList();
                _paraList.Add("@GradeLevelID", item.GradeLevelID);
                _paraList.Add("@SchoolID", item.SchoolID);
                _paraList.Add("Title", item.Title);
                _paraList.Add("ShortName", item.ShortName);
                _paraList.Add("SortOrder", item.SortOrder);
                _paraList.Add("NextGradeLevelID", item.NextGradeLevelID);
                _paraList.Add("@IsActive", item.IsActive);
                _paraList.Add("@CreatedBy", item.CreatedBy);
                _paraList.Add("@ModifiedBy", item.ModifiedBy);

                MyError _error = new MyError();
                Int64 _retValue = _db.Execute("sprocGradeLevelInsertUpdateSingleItem", System.Data.CommandType.StoredProcedure, _paraList, out _error);
                item.Error = _error;

                if (_retValue > 0)
                {
                    item.GradeLevelID = Convert.ToInt32(_retValue);
                    item.Error.ErrorMessage = "Saved successfully!";
                }
                else
                {
                    if (_error.ErrorNo == 0)
                    {
                        item.Error.ErrorNo = 10002;
                        item.Error.ErrorLocation = "GradeLevel";
                        item.Error.ErrorMessage = "No row affected.";
                    }
                }

            }
            catch (Exception Ex)
            {
                item.Error.ErrorNo = 10001;
                item.Error.ErrorLocation = "GradeLevel";
                item.Error.ErrorMessage = Ex.Message;
                item.Error.ErrorStack = Ex.StackTrace;

                MyError.CreateExceptionLog(Ex, "GradeLevel");
            }

            return item;
        }

        private GradeLevel FillData(IDataReader aRow, GradeLevel item)
        {
            try
            {
                item = new GradeLevel();
                item.GradeLevelID = aRow.GetInt32(aRow.GetOrdinal("GradeLevelID"));
                item.SchoolID = aRow.GetInt32(aRow.GetOrdinal("SchoolID"));
                item.Title = aRow.GetString(aRow.GetOrdinal("Title"));

                if (aRow["ShortName"] != DBNull.Value)
                {
                    item.ShortName = aRow.GetString(aRow.GetOrdinal("ShortName"));
                }

                item.NextGradeLevelID = aRow.GetInt32(aRow.GetOrdinal("NextGradeLevelID"));
                if (_db.ColumnExists(aRow, "NextGradeLevelTitle") && aRow["NextGradeLevelTitle"] != DBNull.Value)
                {
                    item.NextGradeLevelTitle = aRow.GetString(aRow.GetOrdinal("NextGradeLevelTitle"));
                }

                if (_db.ColumnExists(aRow, "NextGradeLevelShortName") && aRow["NextGradeLevelShortName"] != DBNull.Value)
                {
                    item.NextGradeLevelShortName = aRow.GetString(aRow.GetOrdinal("NextGradeLevelShortName"));
                }

                item.IsActive = aRow.GetBoolean(aRow.GetOrdinal("IsActive"));
                item.CreatedBy = aRow.GetInt32(aRow.GetOrdinal("CreatedBy"));
                if (_db.ColumnExists(aRow, "CreatedByName") && aRow["CreatedByName"] != DBNull.Value)
                {
                    item.CreatedByName = aRow.GetString(aRow.GetOrdinal("CreatedByName"));
                }
                item.CreatedDate = aRow.GetDateTime(aRow.GetOrdinal("CreatedDate"));
                item.ModifiedBy = aRow.GetInt32(aRow.GetOrdinal("ModifiedBy"));
                if (_db.ColumnExists(aRow, "ModifiedByName") && aRow["ModifiedByName"] != DBNull.Value)
                {
                    item.ModifiedByName = aRow.GetString(aRow.GetOrdinal("ModifiedByName"));
                }
                item.ModifiedDate = aRow.GetDateTime(aRow.GetOrdinal("ModifiedDate"));
            }
            catch (Exception Ex)
            {
                item.Error.ErrorNo = 10003;
                item.Error.ErrorLocation = "GradeLevel";
                item.Error.ErrorMessage = Ex.Message;
                item.Error.ErrorStack = Ex.StackTrace;

                MyError.CreateExceptionLog(Ex, "GradeLevel");
            }

            return item;
        }
    }
}
